package birs.controller.student.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.controller.student.model.STD001Model;
import birs.controller.student.model.STD001ModelA;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class STD001Task {
	
	Connection con = null;
	
	public STD001Task(Connection con){
		this.con = con;
	}
	
	public void getStudentAmount(HttpServletRequest request,HttpServletResponse response) throws SQLException, IOException{
		ResultSetMapper<STD001Model> resultSetMapper = new ResultSetMapper<STD001Model>();
		List<STD001Model> std001ModelList = resultSetMapper.mapRersultSetToObject(getStudentAmountSQL(), STD001Model.class);
		
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(std001ModelList);
		sendResponse(request,response,resultJson);
	}
	
	public void getFirstDrilldown(HttpServletRequest request,HttpServletResponse response) throws SQLException, IOException{
		ResultSetMapper<STD001ModelA> resultSetMapper = new ResultSetMapper<STD001ModelA>();
		List<STD001ModelA> std001ModelAList = resultSetMapper.mapRersultSetToObject(getFirstDrilldownSQL(request.getParameter("period"), request.getParameter("acadYear")), STD001ModelA.class);
		
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(std001ModelAList);
		sendResponse(request,response,resultJson);
	}
	
	private void sendResponse(HttpServletRequest request,HttpServletResponse response,String resultJson) throws IOException{
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getStudentAmountSQL() throws SQLException{
		String sql = " 	(SELECT SM.STUDYPERIOD,SS.ACADYEAR,COUNT(SS.STUDENTID) AS AMOUNT	"
				+" 	FROM  STUDENTSTATUS SS, STUDENTMASTER SM	"
				+" 	WHERE SS.STUDENTSTATUS IN ('10','11','12','13','14')	"
				+" 	AND SS.STUDENTID = SM.STUDENTID	"
				+" 	AND SM.STUDYPERIOD = '1'	"
				+" 	AND SS.SEMESTER = '1'	"
				+" 	AND SS.ACADYEAR > 2550	"
				+" 	GROUP BY SM.STUDYPERIOD,SS.ACADYEAR	"
				
				+" 	UNION ALL	"
				
				+" 	SELECT SM.STUDYPERIOD,SS.ACADYEAR,COUNT(SS.STUDENTID) AS AMOUNT	"
				+" 	FROM  STUDENTSTATUS SS, STUDENTMASTER SM	"
				+" 	WHERE SS.STUDENTSTATUS IN ('10','11','12','13','14')	"
				+" 	AND SS.STUDENTID = SM.STUDENTID	"
				+" 	AND SM.STUDYPERIOD = '2'	"
				+" 	AND SS.SEMESTER = '1'	"
				+" 	AND SS.ACADYEAR > 2550	"
				+" 	GROUP BY SM.STUDYPERIOD,SS.ACADYEAR	"

				+" 	UNION ALL	"

				+" 	SELECT '3' AS STUDYPERIOD,SS.ACADYEAR,COUNT(SS.STUDENTID) AS AMOUNT	"
				+" 	FROM  STUDENTSTATUS SS, STUDENTMASTER SM	"
				+" 	WHERE SS.STUDENTSTATUS IN ('10','11','12','13','14')	"
				+" 	AND SS.STUDENTID = SM.STUDENTID	"
				+" 	AND SM.STUDYPERIOD IN (1,2)	"
				+" 	AND SS.SEMESTER = '1'	"
				+" 	AND SS.ACADYEAR > 2550	"
				+" 	GROUP BY SM.STUDYPERIOD,SS.ACADYEAR)	"
				+" 	ORDER BY STUDYPERIOD,ACADYEAR ";
		return getData(sql);
	}
	
	private ResultSet getFirstDrilldownSQL(String period, String acadYear) throws SQLException{
		String sql = " 	SELECT FAC.FACULTYID, FAC.FACULTYNAME, COUNT(SS.STUDENTID) AS AMOUNT	"
				+" 	FROM STUDENTSTATUS SS, STUDENTMASTER SM, FACULTY FAC	"
				+" 	WHERE  SS.STUDENTSTATUS IN ('10','11','12','13','14')	"
				+" 	AND SS.SEMESTER         = '1'	";
				if("3".equals(period)){
					sql = sql + " AND SM.STUDYPERIOD     IN (1,2) "
						+" AND SS.ACADYEAR         > 2550 ";
				}else{
					sql = sql + " AND SM.STUDYPERIOD = "+period
						+" AND SS.ACADYEAR = "+acadYear;
				}
				sql= sql +" 	AND SS.STUDENTID = SM.STUDENTID	"
				+" 	AND SM.FACULTYID = FAC.FACULTYID	"
				+" 	GROUP BY FAC.FACULTYID, FAC.FACULTYNAME	"
				+" 	ORDER BY AMOUNT	";
		return getData(sql);
	}
	
	private ResultSet getData(String sql) throws SQLException {
		ResultSet result = null;
		
		try {
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
}